package dao;

import bean.Course;
import bean.User;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/*

 */

public class CourseDao {
    public CourseDao() {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public Connection getConnection() throws SQLException {
        return DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/coursejg?characterEncoding=UTF-8&serverTimezone=Asia/Shanghai", "root",
                "1234");
    }

//    public List<Course> list() {
//        return list(0, Short.MAX_VALUE);
//    }

    public List<Course> list(int start, int count,User user) {
        List<Course> courseArrayList = new ArrayList<Course>();

//        String sql = "select * from course order by id desc limit ?,? ";

        int id = user.getId();


        String sql = "SELECT course.*, user.name AS user_name FROM course_user INNER JOIN course ON course_user.course_id = course.id INNER JOIN user ON course_user.user_id = user.id WHERE user.id = ? LIMIT ?, ?;";

        try (Connection c = getConnection(); PreparedStatement ps = c.prepareStatement(sql);) {

            ps.setInt(1, id); // 设置用户ID，假设为1
            ps.setInt(2, start);
            ps.setInt(3, count);

            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                Course course = new Course();
                id = rs.getInt(1);
                String courseInfo = rs.getString(2);
                String week = rs.getString(3);
                int lessonStart = Integer.parseInt(rs.getString(4));
                int lessonEnd = Integer.parseInt(rs.getString(5));
                String day_of_week = rs.getString(6);
                String date = rs.getString(7);
                String teacher = rs.getString(8);
                String place = rs.getString(9);

                course.setId(id);
                course.setCourseInfo(courseInfo);
                course.setWeek(week);
                course.setLessonStart(lessonStart);
                course.setLessonEnd(lessonEnd);

                // 新增项
                course.setDay_of_week(day_of_week);
                course.setDate(date);
                course.setTeacher(teacher);
                course.setPlace(place);

                System.out.println(course);// 打印课程信息

                courseArrayList.add(course);
            }
        } catch (SQLException e) {

            e.printStackTrace();
        }
        return courseArrayList;
    }

    public int getTotal() {
        int total = 0;
        try (Connection c = getConnection(); Statement s = c.createStatement();) {

            String sql = "select count(*) from course";

            ResultSet rs = s.executeQuery(sql);
            while (rs.next()) {
                total = rs.getInt(1);
            }

        } catch (SQLException e) {

            e.printStackTrace();
        }
        return total;
    }

    public void add(Course course,User user) {

        String sql = "insert into course (courseInfo, week, lessonStart, lessonEnd,day_of_week,date,teacher,place) values (?,?,?,?,?,?,?,?)";
        try (Connection c = getConnection(); PreparedStatement ps = c.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);) {
            ps.setString(1, course.getCourseInfo());
            ps.setString(2, course.getWeek());
            ps.setInt(3, course.getLessonStart());
            ps.setInt(4, course.getLessonEnd());

            // 新增字段
            ps.setString(5, course.getDay_of_week());
            ps.setString(6, course.getDate());
            ps.setString(7, course.getTeacher());
            ps.setString(8, course.getPlace());

            int affectedRows = ps.executeUpdate();

            if (affectedRows > 0) {
                ResultSet rs = ps.getGeneratedKeys();
                if (rs.next()) {
                    int course_id = rs.getInt(1);
                    course.setId(course_id);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }


        // 获取用户id
        int user_id = user.getId();
        // 获取课程id
        int course_id = course.getId();

        sql = "INSERT INTO course_user (course_id, user_id) VALUES (?, ?);";
        try (Connection c = getConnection(); PreparedStatement ps = c.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);) {
            ps.setInt(1,course_id);
            ps.setInt(2,user_id);

            int affectedRows = ps.executeUpdate();

            if (affectedRows > 0) {
                ResultSet rs = ps.getGeneratedKeys();
                if (rs.next()) {
                    int id = rs.getInt(1);
                    course.setId(id);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }


    }



    public List<Course> searchByCourseInfo(String searchQuery,User user) {

        List<Course> courses = new ArrayList<>();

        String sql = "select  * \n" +
                "from (\n" +
                "\tselect * \n" +
                "\tfrom course \n" +
                "\twhere id in(select course_id from course_user where user_id = ?)\n" +
                ") as filtered_courses\n" +
                "WHERE courseInfo LIKE ?;";

        try (Connection c = getConnection();
             PreparedStatement ps = c.prepareStatement(sql)) {

            // 设置包含通配符
            ps.setInt(1, user.getId());
            ps.setString(2, "%" + searchQuery + "%");

            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                Course course = new Course();
                int id = rs.getInt(1);
                String courseInfo = rs.getString(2);
                String week = rs.getString(3);
                int lessonStart = Integer.parseInt(rs.getString(4));
                int lessonEnd = Integer.parseInt(rs.getString(5));
                String day_of_week = rs.getString(6);
                String date = rs.getString(7);
                String teacher = rs.getString(8);
                String place = rs.getString(9);

                course.setId(id);
                course.setCourseInfo(courseInfo);
                course.setWeek(week);
                course.setLessonStart(lessonStart);
                course.setLessonEnd(lessonEnd);

                // 新增项
                course.setDay_of_week(day_of_week);
                course.setDate(date);
                course.setTeacher(teacher);
                course.setPlace(place);
                courses.add(course);

            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return courses;
    }

    public void update(Course course) {
        // id	courseInfo	week	lessonStart	lessonEnd

        String sql = "update course set courseInfo= ?, week = ?,lessonStart = ? ,lessonEnd = ?, day_of_week = ?, date = ?, teacher = ?, place = ? where id = ?";
        try (Connection c = getConnection(); PreparedStatement ps = c.prepareStatement(sql);) {

            ps.setString(1, course.getCourseInfo());
            ps.setString(2, course.getWeek());
            ps.setInt(3, course.getLessonStart());
            ps.setInt(4, course.getLessonEnd());

            ps.setString(5, course.getDay_of_week());
            ps.setString(6, course.getDate());
            ps.setString(7, course.getTeacher());
            ps.setString(8, course.getPlace());

            ps.setInt(9, course.getId());

            ps.execute();

        } catch (SQLException e) {

            e.printStackTrace();
        }

    }


    public void delete(Course course,User user) {

        // 获取用户id
        int user_id = user.getId();
        // 获取课程id
        int course_id = course.getId();

        try (Connection c = getConnection(); Statement s = c.createStatement();) {

            String sql = "delete from course_user where user_id = " + user_id + " and course_id =" + course_id;
            s.execute(sql);

        } catch (SQLException e) {

            e.printStackTrace();
        }



        try (Connection c = getConnection(); Statement s = c.createStatement();) {

            String sql = "delete from course where id =" + course_id;
            s.execute(sql);

        } catch (SQLException e) {

            e.printStackTrace();
        }
    }

    public Course get(int id) {
        Course course = null;
        try (Connection c = getConnection(); Statement s = c.createStatement();) {

            String sql = "select * from course where id =" + id;

            ResultSet rs = s.executeQuery(sql);

            if (rs.next()) {

                course = new Course();
                String courseInfo = rs.getString(2);
                String week = rs.getString(3);
                int lessonStart = Integer.parseInt(rs.getString(4));
                int lessonEnd = Integer.parseInt(rs.getString(5));
                String day_of_week = rs.getString(6);
                String date = rs.getString(7);
                String teacher = rs.getString(8);
                String place = rs.getString(9);

                course.setId(id);
                course.setCourseInfo(courseInfo);
                course.setWeek(week);
                course.setLessonStart(lessonStart);
                course.setLessonEnd(lessonEnd);

                // 新增项
                course.setDay_of_week(day_of_week);
                course.setDate(date);
                course.setTeacher(teacher);
                course.setPlace(place);

            }

        } catch (SQLException e) {

            e.printStackTrace();
        }
        return course;
    }
}
